using System;
using System.Collections.Generic;
using System.Configuration;
using System.Dynamic;
using System.Linq;
using System.Threading.Tasks;
using MySqlConnector;
using RFS.BizLogic.utils;
using RFS.Domain.PaiShi;
using RFS.Domain.PaiShi.Accounting;
using RFS.Domain.PaiShi.Zuhuo;
using RFS.Domain.Sys;
using ycx;

namespace RFS.BizLogic.PaiShi.Ports.Adapters
{
    public class RepoProductsMySqlAdapter : IRepoPaiShi
    {
        private string _connectionString = "";

        #region views

        Dictionary<string, string> _views2Creeate = new Dictionary<string, string>()
        {
            {
                "v_email_交易明细表",
                @"
with `rankedrecords` as (select `t`.`id`                                                           AS `id`,
                                `t`.`日期`                                                         AS `日期`,
                                `t`.`供货批次`                                                     AS `供货批次`,
                                `t`.`交易批次`                                                     AS `交易批次`,
                                `t`.`状态`                                                         AS `状态`,
                                `t`.`供货单号`                                                     AS `供货单号`,
                                `t`.`台车号`                                                       AS `台车号`,
                                `t`.`位置号`                                                       AS `位置号`,
                                `t`.`供货代码`                                                     AS `供货代码`,
                                `t`.`供货名称`                                                     AS `供货名称`,
                                `t`.`品种代码`                                                     AS `品种代码`,
                                `t`.`品种名称`                                                     AS `品种名称`,
                                `t`.`品类代码`                                                     AS `品类代码`,
                                `t`.`品类名称`                                                     AS `品类名称`,
                                `t`.`供货底价`                                                     AS `供货底价`,
                                `t`.`物资代码`                                                     AS `物资代码`,
                                `t`.`物资名称`                                                     AS `物资名称`,
                                `t`.`数量`                                                         AS `数量`,
                                `t`.`等级`                                                         AS `等级`,
                                `t`.`缺陷A`                                                        AS `缺陷A`,
                                `t`.`缺陷B`                                                        AS `缺陷B`,
                                `t`.`长度`                                                         AS `长度`,
                                row_number() OVER (PARTITION BY `t`.`供货批次` ORDER BY `t`.`id` ) AS `rn`
                         from `tr`.`email_交易明细表` `t`)
select `rankedrecords`.`id`       AS `id`,
       `rankedrecords`.`日期`     AS `日期`,
       `rankedrecords`.`供货批次` AS `供货批次`,
       `rankedrecords`.`交易批次` AS `交易批次`,
       `rankedrecords`.`状态`     AS `状态`,
       `rankedrecords`.`供货单号` AS `供货单号`,
       `rankedrecords`.`台车号`   AS `台车号`,
       `rankedrecords`.`位置号`   AS `位置号`,
       `rankedrecords`.`供货代码` AS `供货代码`,
       `rankedrecords`.`供货名称` AS `供货名称`,
       `rankedrecords`.`品种代码` AS `品种代码`,
       `rankedrecords`.`品种名称` AS `品种名称`,
       `rankedrecords`.`品类代码` AS `品类代码`,
       `rankedrecords`.`品类名称` AS `品类名称`,
       `rankedrecords`.`供货底价` AS `供货底价`,
       `rankedrecords`.`物资代码` AS `物资代码`,
       `rankedrecords`.`物资名称` AS `物资名称`,
       `rankedrecords`.`数量`     AS `数量`,
       `rankedrecords`.`等级`     AS `等级`,
       `rankedrecords`.`缺陷A`    AS `缺陷A`,
       `rankedrecords`.`缺陷B`    AS `缺陷B`,
       `rankedrecords`.`长度`     AS `长度`,
       `rankedrecords`.`rn`       AS `rn`
from `rankedrecords`
where (`rankedrecords`.`rn` = 1)
order by `rankedrecords`.`id` desc
"
            },
            {
                "v_py_交易明细表", @"
with `rankedrecords` as (select `t`.`id`                                                           AS `id`,
                                `t`.`创建时间`                                                     AS `创建时间`,
                                `t`.`日期`                                                         AS `日期`,
                                `t`.`交易序号`                                                     AS `交易序号`,
                                `t`.`供货商`                                                       AS `供货商`,
                                `t`.`名称`                                                         AS `名称`,
                                `t`.`品种`                                                         AS `品种`,
                                `t`.`供货底价`                                                     AS `供货底价`,
                                `t`.`等级`                                                         AS `等级`,
                                `t`.`瑕疵A`                                                        AS `瑕疵A`,
                                `t`.`瑕疵B`                                                        AS `瑕疵B`,
                                `t`.`瑕疵C`                                                        AS `瑕疵C`,
                                `t`.`长度`                                                         AS `长度`,
                                `t`.`成熟度`                                                       AS `成熟度`,
                                `t`.`重量`                                                         AS `重量`,
                                `t`.`数量`                                                         AS `数量`,
                                `t`.`价格`                                                         AS `价格`,
                                `t`.`成交额`                                                       AS `成交额`,
                                `t`.`佣金`                                                         AS `佣金`,
                                `t`.`划账金额`                                                     AS `划账金额`,
                                `t`.`成交类型`                                                     AS `成交类型`,
                                row_number() OVER (PARTITION BY `t`.`交易序号` ORDER BY `t`.`id` ) AS `rn`
                         from `tr`.`py_交易明细表` `t`)
select `rankedrecords`.`id`       AS `id`,
       `rankedrecords`.`创建时间` AS `创建时间`,
       `rankedrecords`.`日期`     AS `日期`,
       `rankedrecords`.`交易序号` AS `交易序号`,
       `rankedrecords`.`供货商`   AS `供货商`,
       `rankedrecords`.`名称`     AS `名称`,
       `rankedrecords`.`品种`     AS `品种`,
       `rankedrecords`.`供货底价` AS `供货底价`,
       `rankedrecords`.`等级`     AS `等级`,
       `rankedrecords`.`瑕疵A`    AS `瑕疵A`,
       `rankedrecords`.`瑕疵B`    AS `瑕疵B`,
       `rankedrecords`.`瑕疵C`    AS `瑕疵C`,
       `rankedrecords`.`长度`     AS `长度`,
       `rankedrecords`.`成熟度`   AS `成熟度`,
       `rankedrecords`.`重量`     AS `重量`,
       `rankedrecords`.`数量`     AS `数量`,
       `rankedrecords`.`价格`     AS `价格`,
       `rankedrecords`.`成交额`   AS `成交额`,
       `rankedrecords`.`佣金`     AS `佣金`,
       `rankedrecords`.`划账金额` AS `划账金额`,
       `rankedrecords`.`成交类型` AS `成交类型`,
       `rankedrecords`.`rn`       AS `rn`
from `rankedrecords`
where (`rankedrecords`.`rn` = 1)
order by `rankedrecords`.`id` desc
"
            },
            {
                "v_py_供货明细表", @"
with `rankedrecords` as (select `g1`.`id`                                                            AS `id`,
                                `g1`.`创建时间`                                                      AS `创建时间`,
                                `g1`.`供货来源`                                                      AS `供货来源`,
                                `g1`.`供货批次`                                                      AS `供货批次`,
                                `g1`.`供货商`                                                        AS `供货商`,
                                `g1`.`名称`                                                          AS `名称`,
                                `g1`.`品种`                                                          AS `品种`,
                                `g1`.`供货底价`                                                      AS `供货底价`,
                                `g1`.`数量`                                                          AS `数量`,
                                `g1`.`等级`                                                          AS `等级`,
                                `g1`.`瑕疵A`                                                         AS `瑕疵A`,
                                `g1`.`瑕疵B`                                                         AS `瑕疵B`,
                                `g1`.`瑕疵C`                                                         AS `瑕疵C`,
                                `g1`.`长度`                                                          AS `长度`,
                                `g1`.`成熟度`                                                        AS `成熟度`,
                                `g1`.`重量`                                                          AS `重量`,
                                row_number() OVER (PARTITION BY `g1`.`供货批次` ORDER BY `g1`.`id` ) AS `rn`
                         from `tr`.`py_供货明细表` `g1`)
select `rankedrecords`.`id`       AS `id`,
       `rankedrecords`.`创建时间` AS `创建时间`,
       `rankedrecords`.`供货来源` AS `供货来源`,
       `rankedrecords`.`供货批次` AS `供货批次`,
       `rankedrecords`.`供货商`   AS `供货商`,
       `rankedrecords`.`名称`     AS `名称`,
       `rankedrecords`.`品种`     AS `品种`,
       `rankedrecords`.`供货底价` AS `供货底价`,
       `rankedrecords`.`数量`     AS `数量`,
       `rankedrecords`.`等级`     AS `等级`,
       `rankedrecords`.`瑕疵A`    AS `瑕疵A`,
       `rankedrecords`.`瑕疵B`    AS `瑕疵B`,
       `rankedrecords`.`瑕疵C`    AS `瑕疵C`,
       `rankedrecords`.`长度`     AS `长度`,
       `rankedrecords`.`成熟度`   AS `成熟度`,
       `rankedrecords`.`重量`     AS `重量`,
       `rankedrecords`.`rn`       AS `rn`
from `rankedrecords`
where (`rankedrecords`.`rn` = 1)
order by `rankedrecords`.`id` desc
"
            },
            {
                "v_py_反馈查询", @"
with `rankedrecords` as (select  *, row_number() OVER (PARTITION BY `g1`.`批次` ORDER BY `g1`.`id` ) AS `rn`
                         from `tr`.`py_反馈查询` `g1`)
select * from `rankedrecords`
where (`rankedrecords`.`rn` = 1)
order by `rankedrecords`.`id` desc
"
            },
            {
                "v_py_录入查询", @"
with `rankedrecords` as (select `t`.`id`                                                         AS `id`,
                                `t`.`创建时间`                                                   AS `创建时间`,
                                `t`.`供货单`                                                     AS `供货单`,
                                `t`.`供货商`                                                     AS `供货商`,
                                `t`.`供货商名称`                                                 AS `供货商名称`,
                                `t`.`品类`                                                       AS `品类`,
                                `t`.`品类名称`                                                   AS `品类名称`,
                                `t`.`品种`                                                       AS `品种`,
                                `t`.`品种名称`                                                   AS `品种名称`,
                                `t`.`瑕疵1`                                                      AS `瑕疵1`,
                                `t`.`优势`                                                       AS `优势`,
                                `t`.`长度`                                                       AS `长度`,
                                `t`.`开放度`                                                     AS `开放度`,
                                `t`.`等级`                                                       AS `等级`,
                                `t`.`扎数`                                                       AS `扎数`,
                                `t`.`状态`                                                       AS `状态`,
                                `t`.`质检员`                                                     AS `质检员`,
                                `t`.`质检时间`                                                   AS `质检时间`,
                                `t`.`虚拟台车`                                                   AS `虚拟台车`,
                                `t`.`虚拟货位`                                                   AS `虚拟货位`,
                                row_number() OVER (PARTITION BY `t`.`供货单` ORDER BY `t`.`id` ) AS `rn`
                         from `tr`.`py_录入查询` `t`)
select `rankedrecords`.`id`         AS `id`,
       `rankedrecords`.`创建时间`   AS `创建时间`,
       `rankedrecords`.`供货单`     AS `供货单`,
       `rankedrecords`.`供货商`     AS `供货商`,
       `rankedrecords`.`供货商名称` AS `供货商名称`,
       `rankedrecords`.`品类`       AS `品类`,
       `rankedrecords`.`品类名称`   AS `品类名称`,
       `rankedrecords`.`品种`       AS `品种`,
       `rankedrecords`.`品种名称`   AS `品种名称`,
       `rankedrecords`.`瑕疵1`      AS `瑕疵1`,
       `rankedrecords`.`优势`       AS `优势`,
       `rankedrecords`.`长度`       AS `长度`,
       `rankedrecords`.`开放度`     AS `开放度`,
       `rankedrecords`.`等级`       AS `等级`,
       `rankedrecords`.`扎数`       AS `扎数`,
       `rankedrecords`.`状态`       AS `状态`,
       `rankedrecords`.`质检员`     AS `质检员`,
       `rankedrecords`.`质检时间`   AS `质检时间`,
       `rankedrecords`.`虚拟台车`   AS `虚拟台车`,
       `rankedrecords`.`虚拟货位`   AS `虚拟货位`,
       `rankedrecords`.`rn`         AS `rn`
from `rankedrecords`
where (`rankedrecords`.`rn` = 1)
order by `rankedrecords`.`id` desc
"
            },
            {
                "v_py_投诉明细表", @"
select `t`.`id`         AS `id`,
       `t`.`创建时间`   AS `创建时间`,
       `t`.`时间`       AS `时间`,
       `t`.`交易号`     AS `交易号`,
       `t`.`供货商`     AS `供货商`,
       `t`.`名称`       AS `名称`,
       `t`.`品种`       AS `品种`,
       `t`.`等级`       AS `等级`,
       `t`.`瑕疵`       AS `瑕疵`,
       `t`.`长度`       AS `长度`,
       `t`.`开放度`     AS `开放度`,
       `t`.`重量`       AS `重量`,
       `t`.`原成交数量` AS `原成交数量`,
       `t`.`原成交价`   AS `原成交价`,
       `t`.`原成交额`   AS `原成交额`,
       `t`.`投诉类型`   AS `投诉类型`,
       `t`.`新品种名`   AS `新品种名`,
       `t`.`新等级`     AS `新等级`,
       `t`.`新缺陷`     AS `新缺陷`,
       `t`.`新长度`     AS `新长度`,
       `t`.`新开放度`   AS `新开放度`,
       `t`.`新重量`     AS `新重量`,
       `t`.`处理方式`   AS `处理方式`,
       `t`.`处理量`     AS `处理量`,
       `t`.`处理价格`   AS `处理价格`,
       `t`.`处理金额`   AS `处理金额`,
       `t`.`供货商佣金` AS `供货商佣金`,
       `t`.`划账金额`   AS `划账金额`
from `tr`.`py_投诉明细表` `t`
order by `t`.`id` desc
"
            },
            {
                "v_py_流拍明细表", @"
with `rankedrecords` as (select `t`.`id`                                                           AS `id`,
                                `t`.`创建时间`                                                     AS `创建时间`,
                                `t`.`交易序号`                                                     AS `交易序号`,
                                `t`.`交易日期`                                                     AS `交易日期`,
                                `t`.`流拍类型`                                                     AS `流拍类型`,
                                `t`.`流拍意见`                                                     AS `流拍意见`,
                                `t`.`供货商`                                                       AS `供货商`,
                                `t`.`名称`                                                         AS `名称`,
                                `t`.`品种`                                                         AS `品种`,
                                `t`.`供货底价`                                                     AS `供货底价`,
                                `t`.`等级`                                                         AS `等级`,
                                `t`.`瑕疵A`                                                        AS `瑕疵A`,
                                `t`.`瑕疵B`                                                        AS `瑕疵B`,
                                `t`.`瑕疵C`                                                        AS `瑕疵C`,
                                `t`.`长度`                                                         AS `长度`,
                                `t`.`开放度`                                                       AS `开放度`,
                                `t`.`重量`                                                         AS `重量`,
                                `t`.`数量`                                                         AS `数量`,
                                `t`.`流拍价格`                                                     AS `流拍价格`,
                                `t`.`处理状态`                                                     AS `处理状态`,
                                `t`.`处理方式`                                                     AS `处理方式`,
                                `t`.`处理价格`                                                     AS `处理价格`,
                                `t`.`处理金额`                                                     AS `处理金额`,
                                `t`.`佣金`                                                         AS `佣金`,
                                `t`.`划账金额`                                                     AS `划账金额`,
                                `t`.`处理日期`                                                     AS `处理日期`,
                                row_number() OVER (PARTITION BY `t`.`交易序号` ORDER BY `t`.`id` ) AS `rn`
                         from `tr`.`py_流拍明细表` `t`)
select `rankedrecords`.`id`       AS `id`,
       `rankedrecords`.`创建时间` AS `创建时间`,
       `rankedrecords`.`交易序号` AS `交易序号`,
       `rankedrecords`.`交易日期` AS `交易日期`,
       `rankedrecords`.`流拍类型` AS `流拍类型`,
       `rankedrecords`.`流拍意见` AS `流拍意见`,
       `rankedrecords`.`供货商`   AS `供货商`,
       `rankedrecords`.`名称`     AS `名称`,
       `rankedrecords`.`品种`     AS `品种`,
       `rankedrecords`.`供货底价` AS `供货底价`,
       `rankedrecords`.`等级`     AS `等级`,
       `rankedrecords`.`瑕疵A`    AS `瑕疵A`,
       `rankedrecords`.`瑕疵B`    AS `瑕疵B`,
       `rankedrecords`.`瑕疵C`    AS `瑕疵C`,
       `rankedrecords`.`长度`     AS `长度`,
       `rankedrecords`.`开放度`   AS `开放度`,
       `rankedrecords`.`重量`     AS `重量`,
       `rankedrecords`.`数量`     AS `数量`,
       `rankedrecords`.`流拍价格` AS `流拍价格`,
       `rankedrecords`.`处理状态` AS `处理状态`,
       `rankedrecords`.`处理方式` AS `处理方式`,
       `rankedrecords`.`处理价格` AS `处理价格`,
       `rankedrecords`.`处理金额` AS `处理金额`,
       `rankedrecords`.`佣金`     AS `佣金`,
       `rankedrecords`.`划账金额` AS `划账金额`,
       `rankedrecords`.`处理日期` AS `处理日期`,
       `rankedrecords`.`rn`       AS `rn`
from `rankedrecords`
where (`rankedrecords`.`rn` = 1)
order by `rankedrecords`.`id` desc
"
            },
            {
                "v_py_结算明细表", @"
with `rankedrecords` as (select `t`.`id`                                                           AS `id`,
                                `t`.`创建时间`                                                     AS `创建时间`,
                                `t`.`供货商`                                                       AS `供货商`,
                                `t`.`日期`                                                         AS `日期`,
                                `t`.`流水号`                                                       AS `流水号`,
                                `t`.`购买商`                                                       AS `购买商`,
                                `t`.`名称`                                                         AS `名称`,
                                `t`.`科目名称`                                                     AS `科目名称`,
                                `t`.`应收应付标记`                                                 AS `应收应付标记`,
                                `t`.`金额`                                                         AS `金额`,
                                `t`.`摘要>>>`                                                      AS `摘要>>>`,
                                `t`.`品种`                                                         AS `品种`,
                                `t`.`品种名称`                                                     AS `品种名称`,
                                `t`.`成交量`                                                       AS `成交量`,
                                `t`.`单价`                                                         AS `单价`,
                                `t`.`成交额`                                                       AS `成交额`,
                                `t`.`佣金`                                                         AS `佣金`,
                                `t`.`划账金额`                                                     AS `划账金额`,
                                `t`.`交易序号`                                                     AS `交易序号`,
                                `t`.`发生时间`                                                     AS `发生时间`,
                                row_number() OVER (PARTITION BY `t`.`交易序号` ORDER BY `t`.`id` ) AS `rn`
                         from `tr`.`py_结算明细表_2` `t`
                         where (`t`.`应收应付标记` = '-1-应付'))
select `rankedrecords`.`id`           AS `id`,
       `rankedrecords`.`创建时间`     AS `创建时间`,
       `rankedrecords`.`供货商`       AS `供货商`,
       `rankedrecords`.`日期`         AS `日期`,
       `rankedrecords`.`流水号`       AS `流水号`,
       `rankedrecords`.`购买商`       AS `购买商`,
       `rankedrecords`.`名称`         AS `名称`,
       `rankedrecords`.`科目名称`     AS `科目名称`,
       `rankedrecords`.`应收应付标记` AS `应收应付标记`,
       `rankedrecords`.`金额`         AS `金额`,
       `rankedrecords`.`摘要>>>`      AS `摘要>>>`,
       `rankedrecords`.`品种`         AS `品种`,
       `rankedrecords`.`品种名称`     AS `品种名称`,
       `rankedrecords`.`成交量`       AS `成交量`,
       `rankedrecords`.`单价`         AS `单价`,
       `rankedrecords`.`成交额`       AS `成交额`,
       `rankedrecords`.`佣金`         AS `佣金`,
       `rankedrecords`.`划账金额`     AS `划账金额`,
       `rankedrecords`.`交易序号`     AS `交易序号`,
       `rankedrecords`.`发生时间`     AS `发生时间`,
       `rankedrecords`.`rn`           AS `rn`
from `rankedrecords`
where (`rankedrecords`.`rn` = 1)
order by `rankedrecords`.`id` desc
"
            },
        };

        #endregion

        private ILog _log;
        private IConfigT _config;

        public RepoProductsMySqlAdapter(ILog log, IConfigT config)
        {
            _log = log;
            _config = config;
            string connectionStringName = "cnn";
            _connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

            if (!_config.ConfigSys.ProductRepoInitedCorrectly)
            {
                Task.Run(() => { TryInit(); });
            }
        }

        public List<dynamic> ExecuteQuery(string query)
        {
            List<dynamic> results = new List<dynamic>();


            using (MySqlConnection connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            dynamic obj = new ExpandoObject();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                var fieldName = reader.GetName(i);
                                var fieldValue = reader[i];
                                ((IDictionary<string, object>)obj).Add(fieldName, fieldValue);
                            }

                            results.Add(obj);
                        }
                    }
                }
            }

            return results;
        }


        public IEnumerable<dynamic> GetJsonDataSet(BizFlowNodeEnum dn, DateTime bd, DateTime ed)
        {
            bd = bd == default ? DateTime.Today : bd;
            ed = ed == default ? bd : ed;

            if (dn == BizFlowNodeEnum.YanShou) return getYS( new AccountPeriod(bd,ed) );


            throw new NotImplementedException();
        }
        
        public IEnumerable<dynamic> GetJsonDataSetFahuo(  AccountPeriod date)
        {
          
            return getFH(date);


        }

        private IEnumerable<dynamic> getFH(AccountPeriod date)
        {
            return getlR(date);
        }

        private IEnumerable<dynamic> getYS(AccountPeriod bd)
        {
            var res = ExecuteQuery($@"
            select   dic.日期, t.*,dic.供货单号,dic.交易批次  from v_py_供货明细表 t  join v_email_交易明细表 dic on t.供货批次=dic.供货批次
            where dic.日期>='{bd.BeginDate:yyyy-MM-dd}' and  dic.日期<='{bd.EndDate:yyyy-MM-dd}'
             ".TrimEnd()
            );


            return res;
        }

        private IEnumerable<dynamic> getlR(AccountPeriod bd )
        {
              // bd = bd.AddDays(1);
              // bd = new AccountPeriod(bd.BeginDate.AddDays(1), bd.EndDate.AddDays(1));
              
// inned jiao yi date
            // bd = jiaoyiDate;

            #region check batch time span, must be within 1 day.
//
//             // get oly 
//             var daysrange = ExecuteQuery($@"
//  select    t.质检时间 ,dic.日期 交易时间,dic.供货单号 from v_py_录入查询 t  join v_email_交易明细表 dic on t.供货单=dic.供货单号
//            where dic.日期>='{bd.BeginDate:yyyy-MM-dd}' and dic.日期<='{bd.EndDate:yyyy-MM-dd}'
// ");
//             if (daysrange.Count() < 1) throw new Exception($"【{bd}】没有录入数据");
//
//             var maxt = daysrange.Max(x => (DateTime)x.质检时间);
//             var mint = daysrange.Min(x => (DateTime)x.质检时间);
//
//             if ((maxt - mint).TotalHours > 24)
//             {
//                 _log.Err(new Exception(
//                     $"FUCK! 系统根据业务日期【{bd:d}】，检测到货物验收数据跨天了【{mint.Date:d}->{maxt.Date:d}】 。可能是录入表数据异常，请检查录入表。"));
//             }

            #endregion

            var res = ExecuteQuery($@"
             select    t. * from v_py_录入查询 t    
            where t.质检时间>='{bd.BeginDate }' and    t.质检时间<='{bd.EndDate }'  
             ");


            return res;
        }

        public IEnumerable<dynamic> GetJsonDataSetTotal(BizFlowNodeEnum dn, DateTime bd, DateTime ed)
        {
            bd = bd == default ? DateTime.Today : bd;
            ed = ed == default ? bd : ed;

            if (dn == BizFlowNodeEnum.FaHuo) return GetGHTotal();

            return getJy();
        }

        public void TryInit()
        {
            ExecuteNoneQuerySql($@"
CREATE TABLE IF NOT EXISTS {OrderIdsRecordedInAcc} (
    orderId BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    avsId BIGINT UNSIGNED  ,
    INDEX idx_avsId (avsId)
);
");

            foreach (var kv in _views2Creeate)
            {
                var sql = $@" CREATE OR REPLACE VIEW {kv.Key} AS {kv.Value};";
                ExecuteNoneQuerySql(sql);
            }

            _config.ConfigSys.ProductRepoInitedCorrectly = true;
        }

        public IEnumerable<dynamic> ExecuteSqlQuery(string querysql)
        {
            return ExecuteQuery(querysql);
        }

        public IEnumerable<dynamic> GetJiaoYiPrice(DateTime today)
        {
            var sql = $@"
select jy.日期 , jy.供货商, jy.品种 ,jy.长度, avg(jy.价格) 价格  from v_py_交易明细表 jy
where jy.日期='{today:yyyy-MM-dd}'
group by jy.品种,jy.长度,jy.供货商,jy.日期
order by jy.供货商,jy.品种,jy.长度
";

            return ExecuteQuery(sql);
        }

        public IEnumerable<dynamic> GetUnRecordedInAccJsonDataSet(BizFlowNodeEnum jiaoYi, AccountPeriod period)
        {
            var sql = $@"
select jy.日期 , jy.供货商, jy.品种 ,jy.长度, avg(jy.价格) 价格  from v_py_交易明细表 jy
where jy.日期='{period.BeginDate:yyyy-MM-dd}'
group by jy.品种,jy.长度,jy.供货商,jy.日期
order by jy.供货商,jy.品种,jy.长度
";

            return ExecuteQuery(sql);
        }

        public IDayBasedWorkFlowNodeAccountingVoucherSetWrapper GetAVS(AccountPeriod perio)
        {
            return null;
        }


        const string OrderIdsRecordedInAcc = "tbl_acc_order_ids_recorded_In_acc";

        public void SaveRecordedOrderIds(AVSEntity o)
        {
            var sqldel = $"delete from  {OrderIdsRecordedInAcc} where AvsId  ={o.Id} ";
            ExecuteNoneQuerySql(sqldel);

            var dic = o.ToAccItemAndOrderBasedDicItem();

            var vals = dic.Select(x => $"({x.BizOrderId},{x.AVSId})")
                .JoinToString();
            var sqlIns = $"insert into {OrderIdsRecordedInAcc} (OrderId,avsId)  values    {vals} ";
            ExecuteNoneQuerySql(sqlIns);
        }

        public void DeleteRecordedOrderIds(ulong avsId)
        {
            var sqldel = $"delete from  {OrderIdsRecordedInAcc} where AvsId  ={avsId} ";
            ExecuteNoneQuerySql(sqldel);
        }

        public void ExecuteNoneQuerySql(string sql)
        {
            using (MySqlConnection conn = new MySqlConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        cmd.ExecuteNonQuery();
                        _log?.Debug($"done executed [{cmd.CommandText}].");
                    }
                }
                catch (Exception ex)
                {
                    _log?.Err(new Exception($"faile executed [{sql}].", ex));
                }
            }
        }

        public IEnumerable<dynamic> getJy()
        {
            var json = @"


";


            var ds = "".ToObject<dynamic>();
            return ds;
        }

        public IEnumerable<ZuhuaRecord> GetZuhuoOrderItemSet(AccountPeriod day)
        {
            var sql = $@"
 select zz.*   from acc_tbl_组花主表 zz  
    where 
        Date(zz.ZHTime)>='{day.BeginDate :yyyy-MM-dd}'
       and  Date(zz.ZHTime)<='{day.EndDate :yyyy-MM-dd}'
    order by zz.Id desc
";

            var ds = ExecuteSqlQuery(sql);
            var rs=ds.Select(x => $"{x.EntityJson}".ToObject<ZuhuaRecord>());
            return rs;
            
        }


        public IEnumerable<dynamic> GetGHTotal()
        {
            var sql = @"
SELECT  t.供货来源 ,  sum(t.数量) 数量   
FROM  v_py_供货明细表 t  join tr.v_email_交易明细表 dic on t.供货批次 = dic.供货批次
where dic.日期 ='2024-12-13'  group by t.供货来源

";


            var ds = ExecuteQuery(sql);
            return ds;
        }
    }
}